﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Threading;

namespace RecruitingApplication.Data_Model
{
    public static class DataAccessLayer
    {
        private static RecruitingDatabaseEntities m_db;
        private static ReaderWriterLockSlim m_lock =
            new ReaderWriterLockSlim(LockRecursionPolicy.NoRecursion);

        private static readonly int TIMEOUT = 2000;

        #region Mutex Methods

        private static bool ReadLock()
        {
            return m_lock.TryEnterReadLock(TIMEOUT);
        }

        private static bool WriteLock()
        {
            return m_lock.TryEnterWriteLock(TIMEOUT);
        }

        private static void UnlockRead()
        {
            m_lock.ExitReadLock();
        }

        private static void UnlockWrite()
        {
            m_lock.ExitWriteLock();
        }

        #endregion

        #region DB Open/Close
        /// <summary>
        /// Must be called prior to querying.
        /// </summary>
        public static void OpenDB()
        {
            if (WriteLock())
            {
                m_db = new RecruitingDatabaseEntities();
                UnlockWrite();
            }
        }

        /// <summary>
        /// Should be called after data binding has occurred.
        /// </summary>
        public static void CloseDB()
        {
            if (WriteLock())
            {
                m_db.Dispose();
                m_db = null;
                UnlockWrite();
            }
        }

        #endregion

        #region Check Methods ()

        public static bool CheckCandidate(string _name)
        {
            bool check = false;
            string name = _name.ToLower();

            if (ReadLock())
            {
                foreach (Candidate person in m_db.Candidates)
                {
                    if (person.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase))
                    {
                        check = true;
                    }
                }
                UnlockRead();
            }
            return check;
        }

        public static string CheckCandidatesMatching_Json(string match)
        {
            IQueryable<Candidate> matching = null;

            string json = "{ d: [";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    matching = from cand in m_db.Candidates
                               where cand.Name.ToLower().Contains(match.ToLower())
                               select cand;
                }

                foreach (Candidate person in matching)
                {
                    if (json.Last() == '}')
                    {
                        json += ",";
                    }

                    json += "{'value':'";
                    json += person.Name;
                    json += "', 'itemID':";
                    json += person.ID;
                    json += ", 'email':'";
                    json += person.Email;
                    json += "', 'phone':'";
                    json += person.Phone;
                    json += "'}";
                }

                UnlockRead();
            }

            json += "] }";

            return json;
        }

        #endregion

        #region Get Methods (no changes to db allowed)

        public static IQueryable GetAllStates()
        {
            IQueryable states = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    states = from state in m_db.States
                             orderby state.OrderIndex
                             select new
                             {
                                 Name = state.Name,
                                 ID = state.ID,
                                 NeedAssess = state.NeedAssess,
                                 NeedSchedule = state.NeedSchedule,
                                 MoveOutPermission = state.MoveOutPermission,
                                 OrderIndex = state.OrderIndex,
                                 Count = m_db.States.Count()
                             };
                }

                UnlockRead();
            }

            return states;
        }

        //not used for databinding
        public static Document GetDocumentByID(int docID)
        {
            Document doc = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    doc = (from d in m_db.Documents
                           where d.ID == docID
                           select d).DefaultIfEmpty(null).FirstOrDefault();
                }

                UnlockRead();
            }

            return doc;
        }

        public static IQueryable GetAllPositions()
        {
            IQueryable positions = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    positions = from pos in m_db.Positions
                                select new
                                {
                                    ID = pos.ID,
                                    Name = pos.Name
                                };
                }

                UnlockRead();
            }

            return positions;
        }

        // For search bar
        public static string GetAllCandidatesMatching_Json(string match)
        {
            IQueryable<Candidate> matching = null;

            string json = "[";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    matching = from cand in m_db.Candidates
                               where cand.Name.ToLower().Contains(match.ToLower())
                               select cand;
                }

                foreach (Candidate person in matching)
                {
                    if (json.Length > 1)
                    {
                        json += ",";
                    }

                    json += "{'label' : '";
                    json += person.Name;
                    json += "', 'value':'";
                    json += person.Name;
                    json += "', 'itemID':";
                    json += person.ID;
                    json += "}";
                }

                UnlockRead();
            }

            json += "]";

            return json;
        }

        public static string GetAllPositions_Json() 
        {
            string json = "{ d: [";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    // Second, concatenate Positions
                    json += " [";
                    foreach (Position pos in m_db.Positions)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'value' : '";
                        json += pos.Name + "', ";
                        json += "'itemID' : '";
                        json += pos.ID;
                        json += "'}";
                    }
                    json += "],";
                }
                UnlockRead();
            }
            json += "] }";

            return json;

        
        }

        public static string GetAddAppDlgData_Json()
        {
            string json = "{ d: [";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    // First, concatenate Candidates
                    json += " [";
                    foreach (Candidate cand in m_db.Candidates)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'label' : '";
                        json += cand.Name;
                        json += "', 'value':'";
                        json += cand.Name;
                        json += "', 'itemID':'";
                        json += cand.ID;
                        json += "'}";
                    }
                    json += "], ";

                    // Second, concatenate Positions
                    json += " [";
                    foreach (Position pos in m_db.Positions)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'value' : '";
                        json += pos.Name + "', ";
                        json += "'itemID' : '";
                        json += pos.ID;
                        json += "'}";
                    }
                    json += "],";

                    // Third, concatenate States
                    json += " [";
                    foreach (State st in m_db.States)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'value' : '";
                        json += st.Name;
                        json += "', 'itemID' : '";
                        json += st.ID;
                        json += "'}";
                    }
                    json += "]";
                }
                UnlockRead();
            }
            json += "] }";

            return json;
        }

        public static string GetSearchData_Json()
        {
            string json = "{ d: [";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    // First, concatenate Positions
                    json += " [";
                    json += "{'value' : '";
                    json += "-" + "', ";
                    json += "'itemID' : '";
                    json += -1;
                    json += "'}";
                    foreach (Position pos in m_db.Positions)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'value' : '";
                        json += pos.Name + "', ";
                        json += "'itemID' : '";
                        json += pos.ID;
                        json += "'}";
                    }
                    json += "],";

                    // Second, concatenate States
                    json += " [";
                    json += "{'value' : '";
                    json += "-";
                    json += "', 'itemID' : '";
                    json += -1;
                    json += "'}";
                    foreach (State st in m_db.States)
                    {
                        if (json.Last() == '}')
                        {
                            json += ",";
                        }
                        json += "{'value' : '";
                        json += st.Name;
                        json += "', 'itemID' : '";
                        json += st.ID;
                        json += "'}";
                    }
                    json += "]";
                }
                UnlockRead();
            }
            json += "] }";

            return json;
        }

        // For combobox in Add Application Dialog
        public static string GetAllCandidates_Json()
        {
            string json = "[";

            if (ReadLock())
            {
                if (m_db != null)
                {
                    foreach (Candidate cand in m_db.Candidates)
                    {
                        if (json.Length > 1)
                        {
                            json += ",";
                        }
                        json += "{'label' : '";
                        json += cand.Name;
                        json += "', 'value':'";
                        json += cand.Name;
                        json += "', 'itemID':";
                        json += cand.ID;
                        json += "}";
                    }
                }
                UnlockRead();
            }
            json += "]";

            return json;
        }

        public static string GetAllHeardAbouts_Json()
        {
            string json = "[";

            //json format heardabouts for autocomplete
            //autocomplete needs them in the following format:
            //  [ { "label" : "stringtoappearindropdown",
            //      "value" : "stringthatappearsintheinput"
            //    },
            //    { "label" : "anotherstring",
            //      "value" : "etc etc etc"
            //    }
            //  ]
            //
            //  so we are using the heard about name and description for the label
            //  and just the name for the value

            if (ReadLock())
            {
                if (m_db != null)
                {
                    foreach (HeardAbout item in m_db.HeardAbouts)
                    {
                        if (json.Length > 1)
                        {
                            json += ",";
                        }

                        json += "{'value' : '";
                        json += item.Name + "', 'label' : '";

                        //create label
                        string label = item.Name;
                        if (!string.IsNullOrWhiteSpace(item.Description))
                        {
                            label += "- " + item.Description;
                        }

                        //trim label down
                        if (label.Count() > 30)
                        {
                            label = label.Substring(0, 27) + "...";
                        }

                        json += label + "', 'itemID' : '";
                        json += item.ID + "'}";
                    }
                }

                UnlockRead();
            }

            json += "]";

            return json;
        }

        public static string GetAllCategories_Json()
        {
            string json = "[";

            //json format heardabouts for autocomplete
            //autocomplete needs them in the following format:
            //  [ { "label" : "stringtoappearindropdown",
            //      "value" : "stringthatappearsintheinput"
            //    },
            //    { "label" : "anotherstring",
            //      "value" : "etc etc etc"
            //    }
            //  ]
            //
            //  so we are using the heard about name and description for the label
            //  and just the name for the value

            if (ReadLock())
            {
                if (m_db != null)
                {
                    foreach (DocumentCategory item in m_db.DocumentCategories)
                    {
                        if (json.Length > 1)
                        {
                            json += ",";
                        }

                        json += "{'value' : '";
                        json += item.Name;

                        json += "', 'itemID' : '";
                        json += item.ID + "'}";
                    }
                }

                UnlockRead();
            }

            json += "]";

            return json;
        }

        public static IQueryable GetAllCandidatesForSearch()
        {
            IQueryable candidates = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    candidates = from cand in m_db.Candidates
                                 orderby cand.Name
                                 let app = (from a in cand.Applications
                                            orderby a.DateApplied descending
                                            select a).FirstOrDefault()
                                 select new
                                 {
                                     ID = cand.ID,
                                     Name = cand.Name,
                                     Email = cand.Email,
                                     Phone = cand.Phone,
                                     HeardAboutID = cand.HeardAboutID,
                                     HeardAboutNotes = cand.HeardAboutNotes,
                                     StateName = (app == null) ? "-" : app.State.Name,
                                     IsActive = (app == null) ? false : app.IsActive,
                                     AppPosition = (app == null) ? "-" : app.Position.Name,
                                     StateID = (app == null) ? -1 : app.StateID,
                                     AppPositionID = (app == null) ? -1 : app.PositionID
                                 };
                }

                UnlockRead();
            }

            return candidates;
        }

        public static IQueryable GetAllCandidates()
        {
            IQueryable candidates = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    candidates = from cand in m_db.Candidates
                                 orderby cand.Name
                                 select new
                                 {
                                     ID = cand.ID,
                                     Name = cand.Name,
                                     Email = cand.Email,
                                     Phone = cand.Phone,
                                     HeardAboutID = cand.HeardAboutID,
                                     HeardAboutNotes = cand.HeardAboutNotes
                                 };

                }

                UnlockRead();
            }

            return candidates;
        }

        public static IQueryable GetAllHeardAbouts()
        {
            IQueryable heardAbouts = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    heardAbouts = from ha in m_db.HeardAbouts
                                  orderby ha.Name
                                  select new
                                  {
                                      ID = ha.ID,
                                      Name = ha.Name,
                                      Description = ha.Description
                                  };

                }

                UnlockRead();
            }

            return heardAbouts;
        }

        //not used for databinding, can return Candidate
        public static Candidate GetCandidateByID(int id)
        {
            Candidate candidate = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    candidate = (from cand in m_db.Candidates
                                 where cand.ID == id
                                 select cand).DefaultIfEmpty(null).FirstOrDefault();
                }

                UnlockRead();
            }

            return candidate;
        }

        public static int GetCandidateIDByAppID(int appID)
        {
            int candID = -1;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    candID = (from app in m_db.Applications
                              where (app.ID == appID)
                              select app.CandidateID).DefaultIfEmpty(-1).FirstOrDefault();
                }

                UnlockRead();
            }

            return candID;
        }

        public static IQueryable GetAllActiveApplicationsInState(int stateID)
        {
            IQueryable applications = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    applications = from app in m_db.Applications
                                   where (app.IsActive && app.StateID == stateID)
                                   orderby app.StaleDate
                                   select new
                                   {
                                       ID = app.ID,
                                       CandidateName = app.Candidate.Name,
                                       StaleDate = app.StaleDate
                                   };
                }

                UnlockRead();
            }

            return applications;
        }

        public static IQueryable GetDocumentsForApplication(int appID)
        {
            IQueryable docs = null;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    docs = from doc in m_db.Documents
                           where (doc.ApplicationID == appID)
                           select new
                           {
                               ID = doc.ID,
                               CategoryName = doc.DocumentCategory.Name,
                               DateAdded = doc.DateAdded,
                               FileName = doc.FileName,
                               Username = doc.User.Username
                           };
                }

                UnlockRead();
            }

            return docs;
        }

        public static int GetApplicationsCount(int? stateID, int? posID)
        {
            int count = 0;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    count = (from app in m_db.Applications
                             where ((stateID.HasValue && (app.StateID == stateID.Value)) ||
                                (posID.HasValue && (app.PositionID == posID.Value)))
                             select app).Count();
                }

                UnlockRead();
            }

            return count;
        }

        public static int GetCandidatesCount(int heardAboutID)
        {
            int count = 0;

            if (ReadLock())
            {
                if (m_db != null)
                {
                    count = (from cand in m_db.Candidates
                             where (cand.HeardAboutID == heardAboutID)
                             select cand).Count();
                }

                UnlockRead();
            }

            return count;
        }

        public static int GetHeardAbout(string search)
        {
            int foundId = -1;

            if (ReadLock())
            {
                var foundHA = from ha in m_db.HeardAbouts
                              where (ha.Name.Equals(search,
                                                    StringComparison.CurrentCultureIgnoreCase))
                              select ha;

                if (foundHA.Count() > 0)
                {
                    foundId = foundHA.First().ID;
                }

                UnlockRead();
            }

            return foundId;
        }

        public static IQueryable GetApplicationsForCandidate(int candID)
        {
            IQueryable applications = null;
            //IQueryable Documents = null;


            if (ReadLock())
            {
                if (m_db != null)
                {
                    applications = from app in m_db.Applications
                                   where (app.CandidateID == candID)
                                   orderby app.DateApplied descending
                                   select new
                                   {
                                       ID = app.ID,
                                       CandidateName = app.Candidate.Name,
                                       StaleDate = app.StaleDate,
                                       DateApplied = app.DateApplied,
                                       IsActive = app.IsActive,
                                       PositionID = app.PositionID,
                                       StateID = app.StateID,
                                       UserID = app.UserID,
                                       PositionName = app.Position.Name,
                                       StateName = app.State.Name,
                                   };
                }

                UnlockRead();
            }

            return applications;
        }

        #endregion

        #region Set methods (changes made and persisted to db)

        public static void EditCandidate(int candID, string name, string email, string phone, int heardAboutID, string notes)
        {
            if (WriteLock())
            {
                var candidate = from cand in m_db.Candidates
                                where (cand.ID == candID)
                                select cand;

                Candidate targetCand = candidate.First();

                //update state and stale date, since the state changes the 
                //stale date is now
                targetCand.Name = name;
                targetCand.Email = email;
                targetCand.Phone = phone;
                targetCand.HeardAboutID = heardAboutID;
                targetCand.HeardAboutNotes = notes;

                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static void SetApplicationStateID(int ApplicationID, int newStateID)
        {
            if (WriteLock())
            {
                var applications = from app in m_db.Applications
                                   where (app.ID == ApplicationID)
                                   select app;

                Application targetApp = applications.First();

                //update state and stale date, since the state changes the 
                //stale date is now
                targetApp.StateID = newStateID;
                targetApp.StaleDate = DateTime.Now;
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static void SetApplicationPositionID(int ApplicationID, int newPosID)
        {
            if (WriteLock())
            {
                var applications = from app in m_db.Applications
                                   where (app.ID == ApplicationID)
                                   select app;

                Application targetApp = applications.First();

                //update state and stale date, since the state changes the 
                //stale date is now
                targetApp.PositionID = newPosID;
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        /// <summary>
        /// This uses a single function call to update orderindexes
        /// </summary>
        /// <param name="indexes">should contain new orders for ALL states</param>
        public static void SetStateOrder(Dictionary<int, int> indexes)
        {
            if (WriteLock())
            {
                int max = int.MaxValue;

                //set orders to maximum values
                foreach (State state in m_db.States)
                {
                    state.OrderIndex = max--;
                }

                m_db.SaveChanges();//necessary to allow reordering

                foreach (State state in m_db.States)
                {
                    state.OrderIndex = indexes[state.ID];
                }

                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static void SetCandidateName(int candID, string name)
        {
            if (WriteLock())
            {

                // Find Candidate and delete
                Candidate oldcand = (from c in m_db.Candidates
                                     where c.ID == candID
                                     select c).First();

                oldcand.Name = name;

                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        #endregion

        #region Add methods (data created and persisted to db)

        public static void AddApplication(int candID, int posID, int stateID, int userID)
        {
            if (WriteLock())
            {
                Application app = new Application()
                {
                    // ID
                    CandidateID = candID,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    PositionID = posID,
                    StaleDate = DateTime.Now,
                    StateID = stateID,
                    UserID = m_db.Users.First().ID//TODO make this be dynamic!!!
                };

                m_db.Applications.AddObject(app);
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static int AddDocument(int appID, string fileName, byte[] buffer, int catID)
        {
            int id = -1;

            if (WriteLock())
            {
                Document doc = new Document()
                {
                    FileName = fileName,
                    ApplicationID = appID,
                    CategoryID = catID,
                    DateAdded = DateTime.Now,
                    NoteBytes = buffer,
                    UserID = m_db.Users.First().ID//TODO: make user dynamic!!
                };

                m_db.Documents.AddObject(doc);
                m_db.SaveChanges();

                id = doc.ID;

                UnlockWrite();
            }
            return id;
        }

        public static int AddHeardAbout(string name, string description)
        {
            int id = -1;

            if (WriteLock())
            {
                HeardAbout newHA = new HeardAbout()
                {
                    Name = name,
                    Description = description
                };

                m_db.HeardAbouts.AddObject(newHA);
                m_db.SaveChanges();

                id = newHA.ID;

                UnlockWrite();
            }
            return id;
        }

        //public static void AddCandidate(string name, string email, string phone, 
        public static int AddCandidate(string name, string email, string phone,
            int heardAboutID, string notes)
        {
            int returnID = -1;
            if (WriteLock())
            {
                Candidate cand = new Candidate()
                {
                    Name = name,
                    Email = email,
                    Phone = phone,
                    HeardAboutID = heardAboutID,
                    HeardAboutNotes = notes
                };


                m_db.Candidates.AddObject(cand);
                m_db.SaveChanges();

                returnID = cand.ID;

                UnlockWrite();
            }
            return returnID;
        }


        public static int AddState(string name)
        {
            int id = -1;

            if (WriteLock())
            {
                State state = new State()
                {
                    Name = name,
                    MoveOutPermission = 0,//TODO don't hardcode this
                    NeedAssess = false,
                    NeedSchedule = false,
                    OrderIndex = m_db.States.Count()
                };

                m_db.States.AddObject(state);
                m_db.SaveChanges();

                id = state.ID;

                UnlockWrite();
            }

            return id;
        }

        public static int AddPosition(string name)
        {
            int id = -1;

            if (WriteLock())
            {
                Position pos = new Position()
                {
                    Name = name
                };

                m_db.Positions.AddObject(pos);
                m_db.SaveChanges();

                id = pos.ID;

                UnlockWrite();
            }

            return id;
        }

        #endregion

        #region Remove methods (data deleted from db)

        public static void RemoveState(int id)
        {
            if (WriteLock())
            {
                State state = (from s in m_db.States
                               where s.ID == id
                               select s).First();

                m_db.States.DeleteObject(state);
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static void RemoveHeardAbout(int id)
        {
            if (WriteLock())
            {
                HeardAbout heardAbout = (from ha in m_db.HeardAbouts
                                         where ha.ID == id
                                         select ha).First();

                m_db.HeardAbouts.DeleteObject(heardAbout);
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        public static void RemovePosition(int id)
        {
            if (WriteLock())
            {
                Position pos = (from p in m_db.Positions
                                where p.ID == id
                                select p).First();

                m_db.Positions.DeleteObject(pos);
                m_db.SaveChanges();

                UnlockWrite();
            }
        }

        #endregion

        #region Debugging methods (TODO: DELETE BEFORE DEPLOY, comment them out)

        public static void RemoveAllData()
        {
            using (RecruitingDatabaseEntities db = new RecruitingDatabaseEntities())
            {
                foreach (Event e in db.Events.ToArray())
                {
                    db.DeleteObject(e);
                }
                foreach (Note n in db.Notes.ToArray())
                {
                    db.DeleteObject(n);
                }
                foreach (Document d in db.Documents.ToArray())
                {
                    db.DeleteObject(d);
                }
                foreach (DocumentCategory d in db.DocumentCategories.ToArray())
                {
                    db.DeleteObject(d);
                }
                foreach (Application a in db.Applications.ToArray())
                {
                    db.DeleteObject(a);
                }
                foreach (User u in db.Users.ToArray())
                {
                    db.DeleteObject(u);
                }
                foreach (Position p in db.Positions.ToArray())
                {
                    db.DeleteObject(p);
                }
                foreach (State s in db.States.ToArray())
                {
                    db.DeleteObject(s);
                }
                foreach (Candidate c in db.Candidates.ToArray())
                {
                    db.DeleteObject(c);
                }
                foreach (HeardAbout h in db.HeardAbouts.ToArray())
                {
                    db.DeleteObject(h);
                }

                db.SaveChanges();
            }
        }

        public static void GenerateFakeData()
        {
            using (RecruitingDatabaseEntities db = new RecruitingDatabaseEntities())
            {

                #region HeardAbouts

                HeardAbout ha1 = new HeardAbout() { Name = "Career Fair", Description = "" };
                HeardAbout ha2 = new HeardAbout() { Name = "Employee Referral", Description = "" };
                HeardAbout ha3 = new HeardAbout() { Name = "Website", Description = "" };

                db.HeardAbouts.AddObject(ha1);
                db.HeardAbouts.AddObject(ha2);
                db.HeardAbouts.AddObject(ha3);

                #endregion
                #region Candidates

                Candidate cand1 = new Candidate()
                {
                    Email = "fake_email@google.com",
                    HeardAbout = ha1,
                    HeardAboutNotes = "",
                    Name = "Fake Person",
                    Phone = "889-9087"
                };

                Candidate cand2 = new Candidate()
                {
                    Email = "john_doe@google.com",
                    HeardAbout = ha3,
                    HeardAboutNotes = "",
                    Name = "Johnny Boy Doe",
                    Phone = "555-555-5555"
                };

                Candidate cand3 = new Candidate()
                {
                    Email = "somebody@google.com",
                    HeardAbout = ha2,
                    HeardAboutNotes = "",
                    Name = "Santa Claus",
                    Phone = "889-9087"
                };

                Candidate cand4 = new Candidate()
                {
                    Email = "whoknows@google.com",
                    HeardAbout = ha2,
                    HeardAboutNotes = "",
                    Name = "Mr. GoodPerson",
                    Phone = "555-555-5555"
                };

                Candidate cand5 = new Candidate()
                {
                    Email = "anotherone@google.com",
                    HeardAbout = ha3,
                    HeardAboutNotes = "",
                    Name = "Someone McPerson",
                    Phone = "889-9087"
                };

                Candidate cand6 = new Candidate()
                {
                    Email = "_crazy_@google.com",
                    HeardAbout = ha2,
                    HeardAboutNotes = "",
                    Name = "Dat Nguyen",
                    Phone = "555-555-5555"
                };

                db.Candidates.AddObject(cand1);
                db.Candidates.AddObject(cand2);
                db.Candidates.AddObject(cand3);
                db.Candidates.AddObject(cand4);
                db.Candidates.AddObject(cand5);
                db.Candidates.AddObject(cand6);

                #endregion
                #region States

                State st5 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Applied",
                    NeedAssess = true,
                    NeedSchedule = false,
                    OrderIndex = 0
                };

                State st1 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Waiting Phone Interview",
                    NeedAssess = false,
                    NeedSchedule = true,
                    OrderIndex = 1
                };

                State st2 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Completed Phone Interview",
                    NeedAssess = true,
                    NeedSchedule = false,
                    OrderIndex = 2
                };

                State st3 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Waiting Live Interview",
                    NeedAssess = false,
                    NeedSchedule = true,
                    OrderIndex = 3
                };

                State st4 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Completed Live Interview",
                    NeedAssess = true,
                    NeedSchedule = false,
                    OrderIndex = 4
                };

                State st6 = new State()
                {
                    MoveOutPermission = 0,
                    Name = "Offer Made",
                    NeedAssess = false,
                    NeedSchedule = false,
                    OrderIndex = 5
                };


                db.States.AddObject(st1);
                db.States.AddObject(st2);
                db.States.AddObject(st3);
                db.States.AddObject(st4);
                db.States.AddObject(st5);
                db.States.AddObject(st6);

                #endregion
                #region Positions

                Position pos1 = new Position() { Name = "Dev 1" };
                Position pos2 = new Position() { Name = "PT Dev" };

                db.Positions.AddObject(pos1);
                db.Positions.AddObject(pos2);

                #endregion
                #region Users

                User u1 = new User()
                {
                    Email = "j.w.wesson@gmail.com",
                    Permissions = 0,
                    Username = "jwesson",
                    DefaultPage = ""
                };

                User u2 = new User()
                {
                    Email = "user2@gmail.com",
                    Permissions = 0,
                    Username = "user2",
                    DefaultPage = ""
                };

                db.Users.AddObject(u1);
                db.Users.AddObject(u2);

                #endregion
                #region Applications

                Application app1 = new Application()
                {
                    Candidate = cand1,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos1,
                    StaleDate = DateTime.Now,
                    State = st1,
                    User = u1
                };

                Application app2 = new Application()
                {
                    Candidate = cand2,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos2,
                    StaleDate = DateTime.Now.AddDays(-1),
                    State = st2,
                    User = u2
                };

                Application app3 = new Application()
                {
                    Candidate = cand3,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos1,
                    StaleDate = DateTime.Now.AddDays(-2),
                    State = st1,
                    User = u1
                };

                Application app4 = new Application()
                {
                    Candidate = cand4,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos1,
                    StaleDate = DateTime.Now.AddDays(-15),
                    State = st3,
                    User = u2
                };

                Application app5 = new Application()
                {
                    Candidate = cand5,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos1,
                    StaleDate = DateTime.Now.AddDays(-5),
                    State = st3,
                    User = u1
                };

                Application app6 = new Application()
                {
                    Candidate = cand6,
                    DateApplied = DateTime.Now,
                    IsActive = false,
                    Position = pos1,
                    StaleDate = DateTime.Now.AddDays(-10),
                    State = st2,
                    User = u2
                };

                Application app7 = new Application()
                {
                    Candidate = cand6,
                    DateApplied = DateTime.Now,
                    IsActive = true,
                    Position = pos2,
                    StaleDate = DateTime.Now.AddDays(-5),
                    State = st1,
                    User = u2
                };

                db.Applications.AddObject(app1);
                db.Applications.AddObject(app2);
                db.Applications.AddObject(app3);
                db.Applications.AddObject(app4);
                db.Applications.AddObject(app5);
                db.Applications.AddObject(app6);
                db.Applications.AddObject(app7);

                #endregion
                #region Document Categories

                DocumentCategory dc1 = new DocumentCategory() { Name = "Transcript" };
                DocumentCategory dc2 = new DocumentCategory() { Name = "Resume" };
                DocumentCategory dc3 = new DocumentCategory() { Name = "Other" };

                db.DocumentCategories.AddObject(dc1);
                db.DocumentCategories.AddObject(dc2);
                db.DocumentCategories.AddObject(dc3);

                #endregion
                #region Documents

                db.SaveChanges();

                byte[] byteTranscript = Encoding.ASCII.GetBytes("this is a transcript");
                byte[] byteResume = Encoding.ASCII.GetBytes("this is a resume");


                foreach (Application app in db.Applications)
                {
                    Document doc1 = new Document()
                    {
                        Application = app,
                        DateAdded = DateTime.Now,
                        DocumentCategory = dc1,
                        NoteBytes = byteTranscript,
                        FileName = "trans.txt",
                        User = u1
                    };

                    Document doc2 = new Document()
                    {
                        Application = app,
                        DateAdded = DateTime.Now,
                        DocumentCategory = dc2,
                        NoteBytes = byteResume,
                        FileName = "resume.txt",
                        User = u2
                    };

                    db.Documents.AddObject(doc1);
                    db.Documents.AddObject(doc2);
                }

                #endregion
                #region Notes

                /*Note note1 = new Note()
                {
                    Application = app1,
                    DateAdded = DateTime.Now,
                    Document = null,
                    NoteText = "Here's a note!",
                    User = u1
                };

                //if doc is not null, we should look there for Application since
                //there are not guarantees that the Application will match here
                Note note2 = new Note()
                {
                    Application = app2,
                    DateAdded = DateTime.Now,
                    Document = doc2,
                    NoteText = "Here's a note about a document",
                    User = u1
                };

                db.Notes.AddObject(note1);
                db.Notes.AddObject(note2);*/

                #endregion
                #region Events

                Event ev1 = new Event()
                {
                    Application = app1,
                    ContactEmails = "",
                    Date = DateTime.Now.AddDays(15),
                    Description = "",
                    Name = "On site interview",
                    Place = "Conference Room",
                    User = u1
                };

                Event ev2 = new Event()
                {
                    Application = app2,
                    ContactEmails = "j.w.wesson@gmail.com;lskarpa101@gmail.com",
                    Date = DateTime.Now.AddDays(15),
                    Description = "need to conduct another, as first was missed",
                    Name = "Phone interview",
                    Place = "",
                    User = u2
                };

                db.Events.AddObject(ev1);
                db.Events.AddObject(ev2);

                #endregion

                db.SaveChanges();
            }
        }

        #endregion
    }
}